Using Excel to Clean Up Your QuickBooks Customer Lists

The problem is, as time passes, lists tend to get “dirty” in one
way or another. For example, the customer list tends to have incomplete or inconsistent
data. Perhaps some email addresses are missing or customer types are not set
correctly, or the sales tax codes or items are incorrect on several customer
records. These types of problems seem somewhat innocuous on the surface, but
as lists get dirty data entry tends to get inefficient and inaccurate, which
leads to problems with reports, tax returns, and the overall quality of the
QuickBooks data.

Consider the difficulties of filing a sales tax return from QuickBooks data.
If you discover that the sales tax reports in QuickBooks are either wrong or
do not give you enough data to properly fill out the sales tax return, you’ll
have a big project in front of you. Most likely, you’ll have to modify
every invoice and sales receipt for the whole quarter in order to make the reports
give you the accurate and complete information for the tax return. The good
news is that proper list maintenance can help you avoid this type of headache
at tax time.

To avoid these problems, and provide a great service to your clients during
the “slow” time of year, here is a simple, billable service you
can provide for all of your clients. To clean a customer list (or any of the
lists) in QuickBooks, use the following steps to export the list to an IIF file;
then, manipulate the list in Excel and import the list back into QuickBooks.

 

LIST CLEAN UP STEP-BY-STEP

1. Backup your file first — You should always backup a
   data file before working on the data. It’s a good “best practice”
   for all your consulting engagements because it allows you to completely
   undo all of the changes you plan to make. If for whatever reason you do
   need to restore the file to the point before you worked on it, you should
   use the “Restore” command in QuickBooks.
2. Export the customer list to an IIF file — Follow the
   steps below to export your customer list (or any other list you need to
   clean).
3. Open IIF in Excel and use the flexibility
   of Excel filtering to select, sort and modify the list as needed. The example
   below will show how to change the area codes for a group of phone numbers.
4. Import the list back into QuickBooks. This “overwrites”
   the list in the QuickBooks file with any changes you’ve made to the
   list. Note that you cannot delete list entries using this method, only add
   or change existing list items.

EXPORTING LISTS IN IIF FORMAT
To export your lists to an IIF file, follow these steps:

1. Select Utilities from the File menu, then select Export, and then Lists
   to IIF Files.
2. Click the boxes next to Customer List and Vendor List and then click
   OK (see Figure 2).
3. In the Export window, browse to locate the desired folder on your computer.
4. Enter Customers.IIF in the File name field (you may need to replace the
   contents in the field).
5. Click Save (see Figure 3).
6. Click OK when the QuickBooks Information dialog box displays (see Figure
   4).

CLEANING THE LIST IN EXCEL
After you’ve exported the IIF file, open it in Microsoft Excel:

1. Launch the Windows Explorer and browse to the My Documents folder.
2. Right-click on Customers.IIF file and select Open With and then select
   “Microsoft Office Excel,” or if it’s not one of the choices,
   then select “Choose Program.”
3. The next screen lets you have Windows use a “web service”
   to find the appropriate program to open your file, or to have you manually
   select the program yourself. Since the web service won’t work for
   IIF files, choose “Select the program from a list” (Figure 7)
   and click OK.
4. Scroll through the list of your programs and select Microsoft Office
   Excel, and click “Always use the selected program to open this kind
   of file” (Figure 8), and then click OK. As you can see from the Excel
   screen below, the IIF file has several rows and columns that contain the
   various fields of the IIF file. Note that row 21 in this table is a “header”
   row that shows you the name of each of the columns.

From here on, the process of cleaning the list will be greatly aided by your
Excel skills. A very useful feature of Excel for this type of thing is the “Filters.”
Since we’re going to work on all the data in the rows below row 21, it
really makes the task easier if we create a “filter” on row 21.

1. Select row 21 in the IIF table above and then select Filter from the
   Sort and Filter icon in the ribbon. The Filter command makes each of the
   columns in the “Header Row” into special filter fields. Filtered
   data displays only the rows that meet criteria that you specify and hides
   rows that you do not want displayed. After you filter data, you can copy,
   find, edit, format, chart and print the subset of filtered data without
   rearranging or moving it. You can also filter by more than one column. Filters
   are additive, which means that each additional filter is based on the current
   filter and further reduces the subset of data.
2. For our example, we’ll set a filter on the “Phone”
   column to select only those customers who have a phone that begins with
   Area Code 408, and contains the prefix 555. Notice that by clicking on the
   down arrow in each column, you see a menu with all the options for setting
   filters on that column. You could click in the boxes to the left of each
   of the data elements shown, or you can create a “custom filter”
   to have Excel calculate which records to show.
3. After setting the filter on the Phone column to the criteria shown in
   Figure 13, the list now will only display a subset of the records. With
   this filtered list, it’s easy to do mass updating. For example, if
   all of those phone numbers have a new area code, you could use the Find/Replace
   command in Excel to change all of them with one command.
4. In Excel, select Replace from the Find & Select icon.
5. For example, if the area code changes from 408 to 346, enter these numbers
   into the replace command and click Replace All. Now all of the phone numbers
   for the filtered list have been changed. At this point, you can continue
   filtering the list in different ways, making similar modifications until
   you get all the changes made. When you’re finished, save the file
   in the same format as it was (i.e., do not save it as an Excel file).
6. Click the save icon, or press Ctrl+S. On the warning screen below, click
   Yes to continue saving the IIF file in the text (Tab delimited) format.
7. After the file is saved, close it from Excel. Excel will ask you if you
   want to save it again, but this time just click No. You already saved it
   in the format you want in the previous step.

   Now you’re ready to import the modified IIF back into QuickBooks.

IMPORTING IIF FILES
To import an IIF file, follow these steps:

1. From the File menu, select Utilities, then Import, and then IIF Files.
2. In the Import window, browse to locate the desired folder and select
   the Customers.IIF file. Click Open (see Figure 22).
3. QuickBooks displays a dialog box that says, “Your data has been
   imported.” Click OK.

Now you’ve completed the process of exporting the list, making all the
needed changes in Excel, and then importing the list back into QuickBooks. All
your changes can be viewed in the QuickBooks list.

WARNINGS

• Note that, in general, the IIF file format is not recommended. However,
  for this specific example of doing list exports, cleanup and imports, the
  IIF format works great.
• Also, you cannot use this method to modify the “Name” field
  on any of the lists. The name field is the “primary key” for each
  of the lists, and it uses that field to match up the records you import and
  then update the fields with the new data in the IIF file. So if you modify
  the name field in the list, there will be new records added to the list when
  you import the IIF file.
• You cannot use this method to delete names from any list. You can add new
  ones, and modify existing ones only.

I trust this gives you some ideas for new engagements to help your clients
keep their systems clean and efficient. The slow season is a great time to approach
your clients and propose a “clean up” engagement. It’s a great
billable service to help your consulting business, and the clients will benefit
from more efficient data entry (e.g., all the invoices will populate with complete,
accurate information), plus you’ll be able to reduce the likelihood of
big problems at tax time.

Note: QuickBooks saves exported lists with an .IIF extension. The
folder in which you save the .IIF file is not important, though it is best to
choose a folder that you can find easily (e.g., My Documents or the Windows
Desktop).